package com.TP.www.constant;

import com.TP.www.po.User;

import java.util.*;

/**
 * @author 邱彦祥
 * 常量类
 */
public class Constant {

    public static final Scanner SYS_SC = new Scanner(System.in);
    public static final String NAME_REGULAR = "^[a-zA-Z][a-zA-Z0-9_]{4,15}$";
    public static final String PASSWORD_REGULAR = "^[a-zA-Z]\\w{5,17}$";
    public static final String ASSET_REGULAR = "^([1-9][0-9]*)+(\\.[0-9]{1,2})?$";
    public static  User loginUser;

    /**
     * 对商家用户进行操作的sql语句
     */
    public static final String SELECT_MERCHANT_ASSET_BY_ORDERS_ID = "select merchant.id, merchant.name, merchant.password, merchant.asset from merchant,store,orders,goods where orders.id = ? and goods.id = orders.Goods_Orders_Id and store.id = goods.Store_Goods_Id and merchant.id = store.Merchant_Store_Id;";
    public static final String UPDATE_MERCHANT_ASSET ="update merchant set asset = ? where id = ?";
    public static final String UPDATE_CUSTOMER_ASSET = "update customer set asset = ? where id = ?";

    /**
     * 对店铺进行操作的sql语句
     */
    public static final String UPDATE_STORE_ASSET ="update store set asset = ? where id = ?";
    public static final String SELECT_STORE_ASSET_BY_ORDERS_ID = "select merchant.name,store.id, store.storeName, store.intro,store.asset from merchant,store,orders,goods where orders.id = ? and goods.id = orders.Goods_Orders_Id and store.id = goods.Store_Goods_Id and merchant.id = store.Merchant_Store_Id;";
    public static final String SELECT_FROM_STORE_BY_MERCHANT_ID = "select  store.storeName , merchant.name ,store.intro, store.asset, store.id from merchant,store where store.Merchant_Store_Id = ?;";
    public static final String UPDATE_STORE_BY_MERCHANT_ID = "update store set store.storeName = ?,store.intro = ? where Merchant_Store_Id = ?;";
    public static final String CREAT_STORE_BY_MERCHANT_ID = "insert into store (storeName, intro, Merchant_Store_Id) values (?,?,?);";
    public static final String SELECT_FROM_STORE_AND_MERCHANT = "select  store.storeName , merchant.name ,store.intro, store.asset, store.id from merchant,store where store.Merchant_Store_Id = merchant.id;";

    /**
     * 对购物车进行操作的sql语句
     */
    public static final String SELECT_FROM_CART = "select cart.id,cart.goodsNum,customer.name,store.storeName,goods.goodName,goods.price,goods.state from cart,customer,store,goods\n" +
            "where cart.User_Cart_Id = ? and goods.id = cart.Goods_Cart_Id and store.id = goods.Store_Goods_Id;";
    public static final String DELETE_CART_FOR_ID = "delete from cart where cart.id = ?";

    /**
     * 对商品进行操作的sql语句
     */
    public static final String CREAT_GOODS_BY_STORE_ID = "insert into goods (goodName, price, Store_Goods_Id) values (?,?,?);";
    public static final String OFF_GOODS_BY_STORE_ID = "update goods set state = ? where id = ? and Store_Goods_Id = ?;";
    public static final String UPDATE_GOODS_BY_GOODE_ID = "update goods set goodName = ?,price = ? where id = ? and Store_Goods_Id = ?;";
    public static final String SELECT_GOODS_BY_STORE_ID = "select id,goodName,price,state from goods where Store_Goods_Id = ?;";
    public static final String GET_GOOD_ID ="select goods.id,goods.goodName,goods.price,goods.state from goods where goods.goodName = ? and Store_Goods_Id = (select store.id from store where store.storeName = ?);";

    /**
     * 对订单进行操作的sql语句
     */
    public static final String ADD_ORDER = "insert into orders " +
            "(User_Orders_Id,Goods_Orders_Id,nums,price,discount,totalCost,state) " +
            "values (?,?,?,?,?,?,?);";
    public static final String TAKE_ORDERS = "update orders set orders.state = 0 where orders.id = ?;";
    public static final String OVER_ORDERS = "update orders set orders.state = 1 where orders.id = ?;";
    public static final String GET_ORDERS_FOR_CUSTOMER_ID = "select orders.id,customer.name,store.storeName,goods.goodName,orders.nums,orders.price,orders.discount,orders.totalCost,orders.state\n" +
            "from orders,store,customer,goods where orders.User_Orders_Id = ? and orders.Goods_Orders_Id = goods.id and goods.Store_Goods_Id = store.id;";
    public static final String GET_ORDERS_FOR_MERCHANT_ID = "select orders.id,customer.name,store.storeName,goods.goodName,orders.nums,orders.price,orders.discount,orders.totalCost,orders.state from orders,store,customer,goods\n" +
            "where  orders.Goods_Orders_Id = goods.id and goods.Store_Goods_Id = store.id and store.Merchant_Store_Id = ? ;";
    /**
     * 对评论留言进行操作的sql语句
     */
    public static final String ADD_APPRAISE = "insert into appraise (Store_Appraise_Id, User_Appraise_Id, content) values (?,?,?);";
    public static final String GET_APPRAISE_BY_CUSTOMER_ID = "select appraise.id,store.storeName,customer.name,appraise.content from appraise,store,customer where User_Appraise_Id = ? and Store_Appraise_Id = store.id;";
    public static final String GET_APPRAISE_BY_MERCHANT_ID = "select appraise.id,store.storeName,customer.name,appraise.content from appraise,store,customer,merchant where Store_Appraise_Id = store.id and User_Appraise_Id = customer.id and store.Merchant_Store_Id = merchant.id and merchant.id = ? ;";
    public static final String DELETE_APPRAISE_BY_APPRAISE_ID = "delete from appraise where appraise.id = ?";
    public static final String GET_APPRAISE_BY_STORE_ID = "select appraise.id,store.storeName,customer.name,appraise.content from appraise,store,customer where Store_Appraise_Id = store.id and User_Appraise_Id = customer.id and store.id = ? ;";

    /**
     * 对优惠卷进行操作的sql语句
     */
    public static final String GET_COUPON_BY_STORE_ID = "select  coupon.id,coupon.initialNum,coupon.offNum,store.storeName from coupon,store where coupon.Store_Coupon_Id = store.id and store.id = ?;";
    public static final String CREAT_COUPON_BY_STORE_ID = "insert into coupon (Store_Coupon_Id, initialNum, offNum) values (?,?,?);";
    public static final String DELETE_COUPON_BY_COUPON_ID = "delete from coupon where coupon.id = ?";

    /**
     * 对用户的优惠卷进行操作的sql语句
     */
    public static final String DELETE_CUSTOMER_COUPON_BY_CUSTOMER_COUPON_ID = "delete from customerCoupon where customerCoupon.customerCouponId  = ?;";
    public static final String ADD_CUSTOMER_COUPON = "insert into customerCoupon (customerCoupon.Customer_CustomerCoupon_Id, customerCoupon.Coupon_CustomerCoupon_Id) values (?,?);";
    public static final String GET_COUPON_FOR_CUSTOMER = "select customerCoupon.customerCouponId,customer.name,coupon.id,store.storeName,coupon.initialNum,coupon.offNum from customer,customerCoupon,coupon,store where customerCoupon.Customer_CustomerCoupon_Id = customer.id and customerCoupon.Coupon_CustomerCoupon_Id = coupon.id and Store_Coupon_Id = store.id and customer.id = ?;";

    /**
     * 加入购物车时商品的最大数量限制
     */
    public static final int GOOD_MAX_NUM = 50;

}
